package com.wxtf.platform.main.proprietaryInquires;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
/**
 * <li>Title: Married.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.proprietaryInquires</li>
 * <li>Description: 已婚育龄员工</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 21, 2012 10:29:02 AM</li>
 * 
 * @author wangzhi
 * @version 1.0.0.0
 * 
 */
public class Married extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(Married.class);

	public static String from20to49(String temp){
		String from20to49_string="((DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS("+temp+")), '%Y')+0 )>=20 and (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS("+temp+")), '%Y')+0 )<49 or (year(NOW())-49=year("+temp+") and month(NOW())=month("+temp+") and day(NOW())=day("+temp+")) ) ";
		return from20to49_string;
	}
	
	
	public Married(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("select_ba_status_0".equals(this.getMethodName())) {
				msg = this.select_ba_status_0(this.getLogicData());
				return msg;
			} else if("select_ba_status_1".equals(this.getMethodName())) {
				msg = this.select_ba_status_1(this.getLogicData());
				return msg;
			} else if("query1".equals(this.getMethodName())) {
				msg = this.query1(this.getLogicData());
				return msg;
			} else if("select_ba_status_2".equals(this.getMethodName())) {
				msg = this.select_ba_status_2(this.getLogicData());
				return msg;
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}

			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_birthday","tbe_position","tbe_marital_status","tbe_children_status"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
	    String sql=" from tb_employee t1 left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and ((t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and "+from20to49("t1.tbe_birthday")+" and t1.tbe_sex='女' ) or ( t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and  t1.tbe_sex='男' and ("+from20to49("f.tbfi_spouse_birthday")+" or f.tbfi_spouse_birthday is null) ) or (t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and t1.tbe_sex='男' ) ) ";
		if("init".equals(type)) {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			String sex= jsonObject.getString("sex");

			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(!StringUtils.isBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(!StringUtils.isBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}
			if(!StringUtils.isBlank(sex)){
				sql=sql+" and t1.tbe_sex ='"+sex+"' ";
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_position,t1.tbe_marital_status,t1.tbe_children_status" + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
     
	//无生育条件查询
	public Object select_ba_status_0(Object jsonArray) throws Exception {
		String[] fields_ba_status_0 = {
				"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_birthday","tbe_position","tbe_marital_status","tbenhbcr_reason"
		};
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		//String sql=" from tb_employee t1 left join tbe_not_have_birth_con_record t2 on t1.tbe_id=t2.tbe_id where ( DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(t1.tbe_birthday)), '%Y')+0 )>=20 and ( DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(t1.tbe_birthday)), '%Y')+0 )<=49 and (t1.tbe_is_barrenness='1' or t1.the_is_menopause='1' or t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') )  ";		
		String sql=" from tb_employee t1 left join tbe_not_have_birth_con_record t2 on t2.tbe_id=t1.tbe_id left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and ((t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and "+from20to49("t1.tbe_birthday")+" and t1.tbe_sex='女' and (t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') or t1.the_is_menopause='1' or t1.tbe_is_barrenness='1' ) ) or ( t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and  t1.tbe_sex='男' and ("+from20to49("f.tbfi_spouse_birthday")+" or f.tbfi_spouse_birthday is null) and t1.tbe_is_barrenness='1'  )  or  ( t1.tbe_sex='男' and (t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') ) ) ) ";
		
		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String sex= jsonObject.getString("sex");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(!StringUtils.isBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(!StringUtils.isBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}
			if(!StringUtils.isBlank(sex)){
				sql=sql+" and t1.tbe_sex ='"+sex+"' ";
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_position,t1.tbe_marital_status,t2.tbenhbcr_reason" + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields_ba_status_0, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	//有生育条件查询
	public Object select_ba_status_1(Object jsonArray) throws Exception {
		String[] fields_ba_status_1 = {
				"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_birthday","tbe_position","tbe_marital_status","tbe_is_again_birth","tbe_nation"
		};
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		//String sql=" from tb_employee t1 left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and ((t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and ( DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(t1.tbe_birthday)), '%Y')+0 )>=20 and ( DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(t1.tbe_birthday)), '%Y')+0 )<=49 and t1.tbe_sex='女' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.the_is_menopause='0' and t1.tbe_is_barrenness='0' ) or ( t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and  t1.tbe_sex='男' and (DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(f.tbfi_spouse_birthday)), '%Y')+0 )>=20 and ( DATE_FORMAT(FROM_DAYS(TO_DAYS(NOW())-TO_DAYS(f.tbfi_spouse_birthday)), '%Y')+0 )<=49 and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.tbe_is_barrenness='0'  ) ) ";
		String sql=" from tb_employee t1 left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and ((t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and "+from20to49("t1.tbe_birthday")+"  and t1.tbe_sex='女' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.the_is_menopause='0' and t1.tbe_is_barrenness='0' ) or ( t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and  t1.tbe_sex='男' and ("+from20to49("f.tbfi_spouse_birthday")+" or f.tbfi_spouse_birthday is null) and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.tbe_is_barrenness='0'  ) ) ";
		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String sex= jsonObject.getString("sex");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(!StringUtils.isBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(!StringUtils.isBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}
			if(!StringUtils.isBlank(sex)){
				sql=sql+" and t1.tbe_sex ='"+sex+"' ";
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_position,t1.tbe_marital_status,t1.tbe_is_again_birth ,t1.tbe_nation " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields_ba_status_1, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}
	
	//已婚育龄有生育条件妇女应落实节育措施名单
	public Object select_ba_status_2(Object jsonArray) throws Exception {
		String[] fields_ba_status_2 = {
				"reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_birthday","tbe_position","tbe_marital_status","tbe_is_again_birth","tbe_birth_control_measures","tbe_nation"
		};
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		
		String sql=" from tb_employee t1 left join tb_family_base f on f.tbe_id=t1.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617','8A90F0D0176C4961B322BEB26703A322','8A90F0D0176C4961B322BEB26703A323') and "+from20to49("t1.tbe_birthday")+"  and t1.tbe_sex='女' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.the_is_menopause='0' and t1.tbe_is_barrenness='0' and t1.tbe_birth_control_measures in ('104-1','104-2','104-3','104-4','104-5','104-6','104-7') and f.tbfi_id not in (select tbfi_id from tbe_pregnancy_record where tbepr_option in('0','1'))";
		//String sql=" from tb_employee t1,tb_family_base f where f.tbe_id=t1.tbe_id and t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t1.tbe_marital_status in ('8A90F0D0176C4961B322BEB26703A321','EC88549D43494FFE8E277192921D7617') and t1.the_is_menopause='0' and t1.tbe_is_barrenness='0' and  "+from20to49("t1.tbe_birthday")+" and t1.tbe_sex='女'  and t1.tbe_birth_control_measures in ('104-1','104-2','104-3','104-4','104-5','104-6','104-7') ";
		//and f.tbfi_id not in (select tbfi_id from tbe_pregnancy_record where tbepr_option in('0','1'))
		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			String ser_tbe_birth_control_measures = jsonObject.getString("ser_tbe_birth_control_measures");
			if(StringUtils.isBlank(orgIds)){
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgId()+") ";
			} else {
				sql=sql+" and t1.org_id in ("+user.getCurSubOrgIds(orgIds)+") ";
			}
			if(!StringUtils.isBlank(ser_from)){
				sql=sql+" and t1.tbe_birthday >= '"+ser_from+"' ";
			}
			if(!StringUtils.isBlank(ser_to)){
				sql=sql+" and t1.tbe_birthday <= '"+ser_to+"' ";
			}
			if(!StringUtils.isBlank(ser_tbe_birth_control_measures)){
				if("0".equals(ser_tbe_birth_control_measures)){}
				else{
				sql=sql+" and t1.tbe_birth_control_measures ='"+ser_tbe_birth_control_measures+"' ";
				}
			}
		}
		// 排序
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		//String countSql = "select count(*) from  (SELECT t1.*,f.tbfi_id " + sql+") aa left join tbe_pregnancy_record bb on aa.tbfi_id = bb.tbfi_id where bb.tbepr_option not IN('0', '1') or bb.tbepr_option is null ";
		//String querySql = "select aa.reserve1,aa.tbe_id,aa.org_id,aa.tbe_name,aa.tbe_idcard,aa.tbe_sex,aa.tbe_birthday,aa.tbe_position,aa.tbe_marital_status,aa.tbe_is_again_birth ,aa.tbe_birth_control_measures,aa.tbe_nation from  (SELECT t1.*,f.tbfi_id " + sql+") aa left join tbe_pregnancy_record bb on aa.tbfi_id = bb.tbfi_id where bb.tbepr_option not IN('0', '1') or bb.tbepr_option is null ";
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_birthday,t1.tbe_position,t1.tbe_marital_status,t1.tbe_is_again_birth ,t1.tbe_birth_control_measures,t1.tbe_nation " + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields_ba_status_2, countSql, querySql, this.getPage(), this.getPagesize(), null);
	}

	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	//生育史
	 public Object query1(Object jsonArray) throws Exception {
	    	JSONArray array = JSONArray.fromObject(jsonArray);
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String tbe_id = jsonObject.getString("tbe_id");
			String[] field_query1 = {"tbenm_birth_time","tbenm_gravida_num","tbenm_plan_status","tbenm_gravida_times"};
			String sql_query1="select tbenm_birth_time,tbenm_gravida_num,tbenm_plan_status,tbenm_gravida_times from tbe_neonate_main t1 left join tbe_pregnancy_record t2 on t1.tbepr_id=t2.tbepr_id left join tb_family_base t3 on t3.tbfi_id=t2.tbfi_id left join tb_employee t4 on t4.tbe_id=t3.tbe_id  where t4.tbe_id='"+tbe_id+"' order by tbenm_birth_time desc ";
			String countSql="select count(*)  from tbe_neonate_main t1 left join tbe_pregnancy_record t2 on t1.tbepr_id=t2.tbepr_id left join tb_family_base t3 on t3.tbfi_id=t2.tbfi_id left join tb_employee t4 on t4.tbe_id=t3.tbe_id  where t4.tbe_id='"+tbe_id+"' order by tbenm_birth_time desc ";
			return AccessDataBaseUtil.query4SQLPagging(field_query1, countSql, sql_query1, this.getPage(), this.getPagesize(), null);
	 }
	
}


